import re
from django.contrib.auth import get_user_model
from rest_framework import viewsets, mixins, status
from rest_framework.decorators import action
from rest_framework.response import Response

from utils.permissions import IsSuperUser
from utils.inception import Inception
from sqlaudit.models import DatabaseConfig, AuditSQL
from sqlaudit.serializers import DatabaseConfigSerializer, AuditSQLSerializer
from sqlaudit.filter import DatabaseFilter, AuditSQLFilter
from users.serializers import UserSerializer
from sqlaudit.mixins import ActionMxins

User = get_user_model()


class DatabaseConfigViewSet(viewsets.ModelViewSet):
    """
    配置数据库连接信息
    """
    queryset = DatabaseConfig.objects.all()
    serializer_class = DatabaseConfigSerializer
    permission_classes = [IsSuperUser]
    filter_class = DatabaseFilter
    filter_fields = ('name', 'env', 'search')

    def perform_create(self, serializer):
        serializer.create(self.request.data)

    def perform_update(self, serializer):
        serializer.update(self.get_object(), self.request.data)


class FetchSQLTreater(mixins.CreateModelMixin, viewsets.GenericViewSet):
    """
    获取SQL审核人员列表
    create: 返回相应环境和组的审核人列表
    """
    queryset = User.objects.all()
    serializer_class = UserSerializer

    def create(self, request, *args, **kwargs):
        """
        返回相应环境和组的审核人列表
        """
        env = request.data.get('env')
        group_id = request.data.get('group')
        instance = request.user
        if instance.role == 'supremo' or instance.is_superuser or env in ('test', 'dev'):
            # 用户是supremo权限或者提交到测试开发环境、django超级用户发起的，提交人自己审核
            treaters = [self.get_serializer(instance).data]
            return Response(treaters)
        # developer提交可以给manager或者supremo，manager只会提交给supremo审核
        treater_role = ['supremo'] if instance.role == 'manager' else ['manager', 'supremo']
        group = instance.groups.get(pk=group_id)
        if not group:
            return Response(status.HTTP_404_NOT_FOUND)
        treaters = self.serializer_class(group.user_set.filter(role__in=treater_role), many=True).data if group else []
        return Response(treaters)


class AuditSQLCheckView(ActionMxins, mixins.CreateModelMixin, viewsets.GenericViewSet):
    """
    SQL检查序列化类
    """
    queryset = AuditSQL.objects.all()
    serializer_class = AuditSQLSerializer

    forbidden_word_list = ['use ', 'drop ']
    action_type = '--enable-check'  # inception动作为检查，不执行

    result = {'status': 0, 'msg': '', 'data': {}}

    def get_forbidden_words(self, sql_content):
        """
        判断是否有禁用的语法关键字
        """
        forbidden_words = [fword for fword in self.forbidden_word_list if
                           re.search(re.compile(fword, re.I), sql_content)]
        if forbidden_words:
            return forbidden_words
        return None

    @staticmethod
    def check_user_group(request):
        """
        判断选择组是否存在
        """
        group_id = request.data.get('group')
        group = request.user.groups.get(pk=group_id)
        if not group:
            return None
        return group_id

    def create(self, request, *args, **kwargs):
        """
        SQL语句基础检测，通过会创建审核申请，有错误返回错误信息
        """
        request_data = request.data
        group_id_or_none = self.check_user_group(request)

        if not group_id_or_none:
            self.result['status'] = -2
            self.result['msg'] = '审核组不存在，请刷新页面重新选择'
            return Response(self.result, status.HTTP_400_BAD_REQUEST)

        request_data['group'] = group_id_or_none
        request_data['commiter'] = request.user.id
        serializer = self.serializer_class(data=request_data)
        serializer.is_valid(raise_exception=True)
        sql_content = request_data.get('sql_content')
        forbidden_words = self.get_forbidden_words(sql_content)

        if forbidden_words:  # 包含禁用操作
            self.result['status'] = -3
            self.result['msg'] = 'SQL包含禁用操作 {} '.format(forbidden_words)
            return Response(self.result)

        success_sqls, exception_sqls = self.check_execute_sql(request_data.get('database'), sql_content)

        if exception_sqls:
            self.result['msg'] = exception_sqls
            self.result['status'] = -1
            return Response(self.result)

        sql_obj = serializer.save()
        if request.data.get('env') == 'prod':
            self.mail(sql_obj, self.action_type)

        return Response({'status': 0, 'msg': ''}, status.HTTP_201_CREATED)

    def perform_create(self, serializer):
        serializer.create(self.request.data)

    def perform_update(self, serializer):
        serializer.update(self.get_object(), self.request.data)


class AuditSQLMainView(ActionMxins, mixins.ListModelMixin, viewsets.GenericViewSet):
    """
    SQL审核列表，包含执行SQL动作
    list: 列出SQL审核条目列表
    """
    serializer_class = AuditSQLSerializer
    filter_class = AuditSQLFilter
    filter_fields = ('env', 'search', 'user', 'id')
    action_type = '--enable-execute'
    result = {'status': 0, 'msg': '', 'data': {}}

    def filter_daterange(self, query_set):
        """
        过滤创建日期
        日期范围时间参数格式为‘yyyy-M-D,yyyy-M-D’
        """
        daterange = self.request.GET.get('daterange')
        if daterange:
            return query_set.filter(ctime__range=daterange.split(','))
        return query_set

    def get_queryset(self):
        user_obj = self.request.user
        if user_obj.is_superuser:  # 管理员返回全部
            query_set = AuditSQL.objects.all()
        elif user_obj.role in ('supremo', 'manager'):  # 超级和管理身份给他所有组的内容
            query_set = AuditSQL.objects.filter(id=0)  # 取SQL模型一个空的的QuerySet用于后面合并
            for i in user_obj.groups.all():
                query_set = query_set | i.auditsql_set.all()
        else:  # 开发和普通身份给自己提交或者自己审核的，返回合并后的QuerySet
            query_set = user_obj.commiter_user.all() | user_obj.treater_user.all()
        return self.filter_daterange(query_set)

    def check_audit_permission(self, sql_obj):
        user = self.request.user
        if user.is_superuser and sql_obj.env in ('test', 'dev') and user.id == sql_obj.treater.id:
            return True
        if sql_obj.group in user.groups.all():
            if user.role == 'supremo':
                return True
            if user.role == 'manager' and sql_obj.commiter.role not in ('manager', 'supremo'):
                return True
        return False

    @action(detail=True)
    def execute(self, request, *args, **kwargs):
        """
        执行SQL，调用inception，会再次检测SQL是否合法
        """
        sql_obj = self.get_object()
        if not self.check_audit_permission(sql_obj):
            return Response({'status': -3, 'msg': '操作权限不足'})
        if sql_obj.status != -1:
            return Response({'status': -2, 'msg': self.executed})
        affected_rows = 0
        execute_time = 0
        opids = []
        success_sqls, exception_sqls = self.check_execute_sql(sql_obj.database.id, sql_obj.sql_content)
        for success_sql in success_sqls:
            sql_obj.status = 0
            sql_obj.rollback_db = success_sql[8]
            affected_rows += success_sql[6]
            execute_time += float(success_sql[9])
            opids.append(success_sql[7].replace("'", ""))
        if exception_sqls:
            sql_obj.status = 2
            sql_obj.execute_errors = exception_sqls
            self.result['status'] = -1
        sql_obj.rollback_opid = opids
        sql_obj.exe_affected_rows = affected_rows
        self.result['data']['affected_rows'] = affected_rows
        self.result['data']['execute_time'] = '%.3f' % execute_time
        self.result['msg'] = exception_sqls
        self.result['status'] = 0
        self.replace_remark(sql_obj)
        self.mail(sql_obj, self.action_type)
        return Response(self.result)

    @action(detail=True)
    def reject(self, request, *args, **kwargs):
        """
        放弃SQL，不批准上线
        """
        sql_obj = self.get_object()

        if request.user.id == sql_obj.commiter.id:
            sql_obj.status = 1
            self.replace_remark(sql_obj)
            return Response({'status': 0, 'msg': '已经放弃该申请', 'data': {}})
        elif not self.check_audit_permission(sql_obj):
            return Response({'status': -3, 'msg': '操作权限不足'})

        sql_obj.status = 1
        self.replace_remark(sql_obj)
        return Response({'status': 0, 'msg': '已经放弃该申请', 'data': {}})

    @action(detail=True)
    def rollback(self, request, *args, **kwargs):
        """
        回滚SQL的动作，从inception备份库取回滚语句
        """
        sql_obj = self.get_object()

        if not self.check_audit_permission(sql_obj):
            return Response({'status': -3, 'msg': '操作权限不足'})

        db_obj = sql_obj.database
        rollback_opid_list = sql_obj.rollback_opid
        rollback_db = sql_obj.rollback_db
        back_sqls = ''

        for opid in eval(rollback_opid_list)[1:]:
            back_source = 'select tablename from $_$Inception_backup_information$_$ ' \
                          'where opid_time = "{}" '.format(opid)
            back_table = Inception(back_source, rollback_db).get_back_table()
            back_content = 'select rollback_statement from {}.{} ' \
                           'where opid_time = "{}" '.format(rollback_db, back_table, opid)
            back_sqls += Inception(back_content, rollback_db).get_back_sql()

        db_addr = self.get_db_addr(db_obj.user, db_obj.password, db_obj.host, db_obj.port, self.action_type)
        execute_results = Inception(back_sqls, db_obj.name).inception_handle(db_addr).get('result')
        sql_obj.status = -3
        sql_obj.roll_affected_rows = self.result['data']['affected_rows'] = len(execute_results) - 1
        self.replace_remark(sql_obj)
        return Response(self.result)
